"""
Case Type   : 事务-ustore
Case Name   : 事务->子事务->序列->主回滚->重启数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建序列
    2.在事务中查询序列
    3.重启数据库
    4.查询数据
    5.环境清理
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询有数据
    5.成功
History     :
"""
import os
import sys
import unittest

from yat.test import macro
from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.schema = 'schema_ustore_trans'
        self.seq = 'seq_ustore_acid_0380'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)
    
    def testunit_1(self):
        text = '----前置:参数设置;expect:成功----'
        guc_res = self.pri_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG,
            'enable_default_ustore_table=on')
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + text)

        text = '----step1:创建表;expect:成功----'
        self.log.info(text)
        q_str = f'''drop sequence if exists {self.schema}.{self.seq} cascade;
            create sequence {self.schema}.{self.seq} 
            start with 100 increment by 1 maxvalue 102 nocycle;
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.CREATE_SEQUENCE_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)
        
        text = '----step2:在事务中查询序列;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
            select nextval('{self.schema}.{self.seq}');
            savepoint point0380;
            select nextval('{self.schema}.{self.seq}');
            rollback;
            select currval('{self.schema}.{self.seq}');
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('START TRANSACTION', q_res, '执行失败: ' + text)
        self.assertIn('SAVEPOINT', q_res, '执行失败: ' + text)
        self.assertIn('ROLLBACK', q_res, '执行失败: ' + text)
        self.assertIn('100\n', q_res, '执行失败: ' + text)
        self.assertIn('101\n', q_res, '执行失败: ' + text)

        text = '----step3:重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step4:查询数据;expect:102----'
        self.log.info(text)
        q_str = f'''
            select nextval('{self.schema}.{self.seq}');
            select currval('{self.schema}.{self.seq}');
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('102\n', q_res, '执行失败: ' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        guc_res = self.pri_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG,
            'enable_default_ustore_table=off')
        self.log.info(guc_res)

        sql = f'drop sequence {self.schema}.{self.seq} cascade;'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)

        self.assertTrue(guc_res, '执行失败:' + text)
        self.assertIn(self.constant.DROP_SEQUENCE_SUCCESS_MSG, result,
                      '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
